﻿using CCWin;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using TelephoneYiShou;

namespace ku
{
    public partial class FrmInventory : Skin_Mac
    {
        public DataTable dt;
        int a, start;//a表示总行数，start表示当前位置
        int size = 6;//size表示一页显示的数量
        string str1 = "select CommodityName, c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID";
        string str = "server=.;database = TelephoneYiShou;uid = sa;pwd = 123456";
        public FrmInventory()
        {
            InitializeComponent();
        }
        //DGV
        private void Form2_Load(object sender, EventArgs e)
        {
    
            dt = DBHelper.GetDataTable("select CommodityName,c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID ");
            a = dt.Rows.Count;//获取数据的条数
            show(0, size);
            label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a / size + 1, a);//显示数据
        }

        //传值---修改删除
        private void DGV_inventory_Click(object sender, EventArgs e)
        {
            label1.Text = DGV_inventory.SelectedRows[0].Cells[0].Value.ToString();
            label3.Text = DGV_inventory.SelectedRows[0].Cells[1].Value.ToString();
            textBox2.Text = DGV_inventory.SelectedRows[0].Cells[2].Value.ToString();
        }
        //修改库存
        private void button3_Click(object sender, EventArgs e)
        {
            //搜索id
            if (label1.Text== "请选择")
            {
                MessageBoxEx.Show("请先选择要改的商品");
                return;
            }
            string sqlss = string.Format("select commodityid from commodity where commodityname = '{0}' and colour = '{1}'", label1.Text, label3.Text);
            string sqlid = DBHelper.GetDataTable(sqlss).Rows[0][0].ToString();
            //根据id修改库存
            string sql = string.Format("update  Stock set sum = '{0}' where CommodityID = '{1}'", textBox2.Text, sqlid);
            if (DBHelper.ExecuteNonQuery(sql))
            {
                MessageBoxEx.Show("修改成功");
                str1 = "select CommodityName, c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID";
                dt = DBHelper.GetDataTable(str1);
                a = dt.Rows.Count;//获取数据的条数
                show(0, size);
                label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a / size + 1, a);//显示数据
            }
            else
            {
                MessageBoxEx.Show("修改失败");
            }
        }
        //删除库存
        private void skinButton1_Click(object sender, EventArgs e)
        {
            if (label1.Text == "请选择")
            {
                MessageBoxEx.Show("请先选择删除的商品");
                return;
            }
            string sqlss = string.Format("select commodityid from commodity where commodityname = '{0}' and colour = '{1}'", label1.Text, label3.Text);
            string sqlid = DBHelper.GetDataTable(sqlss).Rows[0][0].ToString();
            string sql = string.Format("delete from Stock where CommodityID = '{0}'",  sqlid);
            if (DBHelper.ExecuteNonQuery(sql))
            {
                MessageBoxEx.Show("删除成功");
                //刷新数据库
                str1 = "select CommodityName, c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID";
                dt = DBHelper.GetDataTable(str1);
                a = dt.Rows.Count;//获取数据的条数
                show(0, size);
                label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a / size + 1, a);//显示数据
            }
            else
            {
                MessageBoxEx.Show("删除失败");
            }
        }
        //查询数据库
        private void btn_select_Click_1(object sender, EventArgs e)
        {
            string cnameid = cid.Text;
            //根据型号查询
            if (!string.IsNullOrEmpty(cnameid))
            {
                str1 = string.Format("select CommodityName, c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID and CommodityName like '%{0}%'", cnameid);
                dt = DBHelper.GetDataTable(str1);
                a = dt.Rows.Count;//获取数据的条数
                show(0, size);
                label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a / size + 1, a);//显示数据
            }
            else
            {
                MessageBoxEx.Show("请输入要搜索的内容");
            }
        }
        //退出
        private void button5_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void label9_Click(object sender, EventArgs e)
        {

        }


        //上一页
        private void button1_Click(object sender, EventArgs e)
        {
            if (start < 1)
            {
                MessageBoxEx.Show("已是第一页");
                show(0, size);
            }
            else
            {
                start = start - 1;
                show(start * size, size);
            }
            label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a/ size + 1, a);//显示数据
        }
        //下一页
        private void button2_Click(object sender, EventArgs e)
        {
            if (start + 1 > a / size)
            {
                MessageBoxEx.Show("已是尾页");
                show(start * size, size);
            }
            else
            {
                start = start + 1;
                show(start * size, size);
            }
            label5.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, a / size + 1, a);//显示数据
        }
        //库存总量
        private void button4_Click(object sender, EventArgs e)
        {
            str1 = "select CommodityName, c.Colour,[sum] from stock s,Commodity c where s.CommodityID=c.CommodityID";
            dt = DBHelper.GetDataTable(str1);
            int sum = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sum += int.Parse(dt.Rows[i][2].ToString());
            }
            MessageBoxEx.Show("总库存为："+sum.ToString());
        }


        //show方法
        public void show(int i, int j)
        {
            SqlConnection sqlcon = new SqlConnection(str);
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand(str1, sqlcon);
            SqlDataAdapter sqlada = new SqlDataAdapter(sqlcmd);
            DataSet ds = new DataSet();
            sqlada.Fill(ds, i, j, "[stock]");//把数据库表中的i-j的记录数用来填充数据集
            DGV_inventory.DataSource = ds.Tables["[stock]"];
            ds = null;//清空数据集
        }
    }
    }
    
